package xpn.platform.common.util;

import java.awt.image.BufferedImage;
import java.io.*;
import java.net.URL;
import java.net.URLConnection;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.ParseException;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.*;

import javax.imageio.ImageIO;

public class ExcelUtil {
	private static NumberFormat nf = new DecimalFormat("0.00");

	public static Object export(JSONArray list,JSONArray  colsInfo,Integer beginRow,String dirpath,String filename,XSSFWorkbook book,XSSFSheet sheet){
		File file=null;
		OutputStream os = null;
		String fullpath="";
		String doneloadfileName="";
		JSONObject ret=new JSONObject();
		Map<String,Integer> colMap=new HashMap<String,Integer>();
		try {
			//int startRow=1;
			int startRow=beginRow;
			int startCell=0;
		    String path=dirpath;
		    fullpath=path+"/"+filename+".xlsx";
		    doneloadfileName=filename+".xlsx";
			file=new File(fullpath);
		    File dir = new File(dirpath);
		    if(!dir.exists()){
		    	dir.mkdirs();
		    }
			if(!file.exists()){
				file.createNewFile();
			}
			if (file.createNewFile()){
			    file.setExecutable(true);//设置可执行权限
			    file.setReadable(true);//设置可读权限
			    file.setWritable(true);//设置可写权限
			}
		    int maxColLevel=getMaxLevelOfCols(colsInfo,1);
		    JSONObject rowsStartCellInfo=new JSONObject();//所有行开始列信息
		    for(int i=1;i<=maxColLevel;i++){//初始化所有表头行开始列信息
		    	rowsStartCellInfo.put(i, startCell);
		    }
			// 创建工作区
		    if(book==null){
				book = new XSSFWorkbook();
		    }
			// 创建第一个sheet页
		    if(sheet==null){
				sheet = book.createSheet("sheet1");
		    }
			JSONArray keyCols=new JSONArray();
			JSONObject writeInfo=writeExcelHead(book,sheet,colsInfo,rowsStartCellInfo,maxColLevel,keyCols,startRow,colMap);
			keyCols=writeInfo.getJSONArray("keyCols");
			colMap=(Map)writeInfo.get("colMap");
			writeExcelList(book,sheet,keyCols,list,maxColLevel,startCell,colMap);
			// 输出流，将文件写到对应的目录中
			os = new FileOutputStream(fullpath);
			book.write(os);
			ret.put("filepath", fullpath);
			ret.put("filename", doneloadfileName);
		} catch (IOException e1) {
			e1.printStackTrace();
		}finally{
		    try {
				os.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return ret;
	}

	/*
	* 插入圖片的寬度
	* */
	//将图片插入到指定的单元格中
	public static void picture(XSSFWorkbook workbook,XSSFSheet sheet, String filepath, String fileType,int row, int col,Integer width) {
		InputStream is=null;
		try {
			if(width==null){
				width=200;
			}
			Drawing patriarch = sheet.createDrawingPatriarch();
			//生成一个表格，设置表格名称
			String urlFile = "file:///"+filepath;
			// 构造URL
			URL url = new URL(urlFile);
			// 打开连接
			URLConnection con = url.openConnection();
			//设置请求超时为5s
			con.setConnectTimeout(5 * 1000);
			//int cols = col + 1;
			//int rows = row - 1;
			/*
			// 输入流
			InputStream is = con.getInputStream();
			ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
			BufferedImage bufferImg = ImageIO.read(is);

			if (fileType.equals(".jpg")) {
				ImageIO.write(bufferImg, "jpg", byteArrayOut);
			} else if (fileType.equals(".png")) {
				ImageIO.write(bufferImg, "png", byteArrayOut);
			}
			XSSFClientAnchor anchor1 = new XSSFClientAnchor(0, 0, 200, 100, 1, (short) row, 3, (short) col);
			patriarch.createPicture(anchor1, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
			*/
			is = new FileInputStream(filepath);
			byte[] bytes = IOUtils.toByteArray(is);
			int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
			CreationHelper helper = workbook.getCreationHelper();
			Drawing drawing = sheet.createDrawingPatriarch();
			ClientAnchor anchor1 = helper.createClientAnchor();
            // 图片插入坐标
			anchor1.setCol1(col);
			anchor1.setRow1(row);
            // 插入图片
			Picture pict = drawing.createPicture(anchor1, pictureIdx);
			// 获取原图片的宽度和高度，单位都是像素
			File image = new File(filepath);
			BufferedImage sourceImg = ImageIO.read(image);
			double imageWidth = sourceImg.getWidth();
			double imageHeight = sourceImg.getHeight();
			// 获取单元格宽度和高度，单位都是像素
			XSSFRow rowInfo=sheet.getRow(row);
			XSSFCell cell=rowInfo.getCell(col);
			double cellWidth = sheet.getColumnWidthInPixels(cell.getColumnIndex());
			double cellHeight = cell.getRow().getHeightInPoints() / 72 * 96;// getHeightInPoints()方法获取的是点（磅），就是excel设置的行高，1英寸有72磅，一般显示屏一英寸是96个像素
			XSSFCellStyle imageStyle=(XSSFCellStyle) workbook.createCellStyle();
			System.out.println(sheet.getColumnWidth(col));
			rowInfo.setHeight((short)(150*20));
			sheet.setColumnWidth(col,50*256);
			// 插入图片，如果原图宽度大于最终要求的图片宽度，就按比例缩小，否则展示原图
			if (imageWidth > width) {
				double scaleX = width / cellWidth;// 最终图片大小与单元格宽度的比例
				// 最终图片大小与单元格高度的比例
				// 说一下这个比例的计算方式吧：( imageHeight / imageWidth ) 是原图高于宽的比值，则 ( width * ( imageHeight / imageWidth ) ) 就是最终图片高的比值，
				// 那 ( width * ( imageHeight / imageWidth ) ) / cellHeight 就是所需比例了
				double scaleY = ( width * ( imageHeight / imageWidth ) ) / cellHeight;
				//pict.resize(scaleX, scaleY);
				//sheet.setColumnWidth(col,(int)imageWidth);
				pict.resize(1);
			} else {
				pict.resize(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try{
				is.close();
			}catch (Exception ex){

			}

		}
	}



	//写入excel表头
	public static JSONObject writeExcelHead(Workbook book,XSSFSheet sheet,JSONArray colsInfo,JSONObject rowsStartCellInfo,int maxColLevel,JSONArray keyCols,Integer beginRow,Map<String,Integer> colMap) {
	    JSONObject ret=new JSONObject();
	    XSSFCellStyle styleCenter=(XSSFCellStyle) book.createCellStyle();
	    //新建font实体
	    XSSFFont hssfFont = (XSSFFont) book.createFont();
	    hssfFont.setFontName("黑体");
	    hssfFont.setBold(true);
		styleCenter.setBorderBottom(BorderStyle.THIN);
		styleCenter.setBorderLeft(BorderStyle.THIN);// 左边框
		styleCenter.setBorderTop(BorderStyle.THIN);// 上边框
		styleCenter.setBorderRight(BorderStyle.THIN);// 右边框
		styleCenter.setAlignment(HorizontalAlignment.CENTER);
		int rows=0;
		for(int i=0;i<colsInfo.size();i++){
		  JSONObject col=colsInfo.getJSONObject(i);
		  if(col.containsKey("dataIndex")){
			  if(!col.getString("dataIndex").equals("")){
				  if(!col.getString("dataIndex").equals("null")){
					  keyCols.add(col);
				  }
			  }
		  }
		  int colspan=getColColspan(col,0);
		  int rowspan=getColRowspan(col,maxColLevel);
		  //System.out.println(col.getString("title"));
		  int level=1;
		  if(col.containsKey("_level")){
			  level=col.getInt("_level");
		  }
		  int startRow=beginRow+level-1;
		  Row row =null;
		  if(sheet.getRow(startRow)==null){
			  row=sheet.createRow(startRow);
			  rows++;
		  }else{
			  row=sheet.getRow(startRow);
		  }
		  XSSFCell cell = null;
		  int endRow = startRow+rowspan-1;
		  int endLevel=level+rowspan-1;
		  int startCol = rowsStartCellInfo.getInt(level+"");
		  cell=(XSSFCell) row.createCell(startCol);

		  Integer endCol = startCol+colspan-1;
          if(col.containsKey("color")){
        	  String color=col.getString("color");
        	  if(color!=null&&color.equals("red")){
        		  XSSFFont redFont = (XSSFFont)book.createFont();
        		//颜色
        		redFont.setColor(Font.COLOR_RED);
        		redFont.setFontName("黑体");
        		redFont.setBold(true);
        		styleCenter.setFont(redFont);
        	  }
          }else{
        	  styleCenter.setFont(hssfFont);
          }
		  if(colspan>1||rowspan>1){
			  // 合并单元格
			  CellRangeAddress range= new CellRangeAddress(startRow, endRow, startCol, endCol);
			  sheet.addMergedRegion(range);// 实行列合并
			  setRegionStyle(sheet,range,styleCenter);
		  }
		  cell.setCellStyle(styleCenter);
		  String title="";
		  if(col.containsKey("title")){
			  title=col.getString("title");
		  }
		  if(title!=null&&!title.equals("")){
			  cell.setCellValue(title);
			 // sheet.setColumnWidth(cell.getColumnIndex(), title.getBytes().length*300);
			  if(col.containsKey("dataIndex")){
				  if(!col.getString("dataIndex").equals("")){
					  if(!col.getString("dataIndex").equals("null")){
						  int columnWidth = sheet.getColumnWidth(i)/256;
						  //int columnWidth = sheet.getColumnWidth(i)/170;
						  int length= title.getBytes().length;
		                    if (columnWidth < length) {
		                        columnWidth = length;
		                    }
						  colMap.put(col.getString("dataIndex"), columnWidth);
					  }
				  }
			  }
		  }


		  for(int j=level;j<=endLevel;j++){
			  rowsStartCellInfo.put(j, endCol+1);
		  }
		  if(col.containsKey("children")){
			  writeExcelHead(book,sheet,col.getJSONArray("children"),rowsStartCellInfo,maxColLevel,keyCols,beginRow,colMap);
		  }
	  }
		int endRow=beginRow+rows-1;
		System.out.println("endRow**************************"+endRow);
		int maxCells=sheet.getRow(endRow).getPhysicalNumberOfCells();
		for(int i=1;i<=maxCells;i++){
			sheet.autoSizeColumn(i, true);
		}
	    ret.put("keyCols", keyCols);
	    ret.put("colMap", colMap);
		return ret;
	}

	//写入excel表头
	public static JSONObject writeExcelList(XSSFWorkbook book,XSSFSheet sheet,JSONArray colsInfo,JSONArray list,Integer beginRow,Integer startCell,Map<String,Integer> colMap) {
	    JSONObject ret=new JSONObject();
	    //新建font实体
	    XSSFFont hssfFont = (XSSFFont) book.createFont();
	    Set<String> pictureKey=new HashSet<String>();
	    hssfFont.setFontName("宋体");
		XSSFCellStyle styleCenter = (XSSFCellStyle) book.createCellStyle();
	    styleCenter.setFont(hssfFont);
		styleCenter.setBorderBottom(BorderStyle.THIN);
		styleCenter.setBorderLeft(BorderStyle.THIN);// 左边框
		styleCenter.setBorderTop(BorderStyle.THIN);// 上边框
		styleCenter.setBorderRight(BorderStyle.THIN);// 右边框
		styleCenter.setVerticalAlignment(VerticalAlignment.CENTER);
		styleCenter.setAlignment(HorizontalAlignment.CENTER);
		XSSFCellStyle styleLeft = (XSSFCellStyle) book.createCellStyle();
		XSSFDataFormat df = (XSSFDataFormat) book.createDataFormat();  //此处设置数据格式
		styleLeft.setFont(hssfFont);
		styleLeft.setBorderBottom(BorderStyle.THIN);
		styleLeft.setBorderLeft(BorderStyle.THIN);// 左边框
		styleLeft.setBorderTop(BorderStyle.THIN);// 上边框
		styleLeft.setBorderRight(BorderStyle.THIN);// 右边框
		styleLeft.setAlignment(HorizontalAlignment.LEFT);
		styleLeft.setVerticalAlignment(VerticalAlignment.CENTER);
	    String []cellField=new String[colsInfo.size()];
	    for(int i=0;i<colsInfo.size();i++){
	    	JSONObject col=colsInfo.getJSONObject(i);
	    	if(col.containsKey("dataIndex")&&!col.getString("dataIndex").equals("")){
	    		if(!col.getString("dataIndex").equals("null")){
	            	cellField[i]=col.getString("dataIndex");
	    		}

	    	}
	    }
		for (int i = 0; i < list.size(); i++) {
			JSONObject rowInfo = list.getJSONObject(i);
			if(!rowInfo.containsKey("chilren")){
				beginRow++;
			}

			// 生成第i+1行
			int index=sheet.getLastRowNum();
			Row row = sheet.createRow(index+1);
			// 给第i行的第j列赋值
			for (int j = 0; j < colsInfo.size(); j++) {
				JSONObject col=colsInfo.getJSONObject(j);
				String colKey=col.getString("dataIndex");
				String colType=null;
				if(col.containsKey("type")){
					colType=col.getString("type");
					pictureKey.add(colKey);
				}
				boolean isNumber=false;
				if(col.containsKey("isNumber")){
					isNumber=col.getBoolean("isNumber");
				}
				XSSFCell cell = null;
				cell = (XSSFCell) row.createCell(j+startCell);
				if(!colMap.containsKey(colKey)){
					colMap.put(colKey, 80);
				}
				if (!rowInfo.has(colKey)) {
					if(isNumber){
						styleCenter.setDataFormat(df.getFormat("##0.00"));
					}
					cell.setCellStyle(styleCenter);
				}else{
					String cellV=rowInfo.getString(colKey);
					//处理excel图片插入
					if(colType!=null){
						if(colType.equals("picture")){
							//将图片插入到指定的列
							String fileType=".jpg";
							if(cellV!=null){
								fileType=cellV.substring(cellV.indexOf("."),cellV.length());
							}
							picture(book,sheet,cellV,fileType,index+1,j+startCell,300);
						}
						continue;
					}
					//endOf处理图片插入
					if(("name").equals(colKey)){
						if(rowInfo.containsKey("_level")){
							for(int x=0;x<rowInfo.getInt("_level");x++){
								cellV="  "+cellV;
							}
						}
					}
					int valueLength=cellV.getBytes().length;
					if(colMap.containsKey(colKey)){
						int maxSize = colMap.get(colKey);
						if (valueLength > maxSize) {
							colMap.put(colKey, valueLength);
						}
					}else{
						colMap.put(colKey, valueLength);
					}
					if(cellV==null||"null".equals(cellV)||"".equals(cellV)){

					}else{
						if("name".equals(colKey)){
							cell.setCellValue(cellV);
					        cell.setCellStyle(styleLeft);
						}else{
							try {
								if(isNumber){
									cell.setCellValue(nf.parse(cellV).doubleValue());
									styleCenter.setDataFormat(df.getFormat("##0.00"));
									styleCenter.setAlignment(HorizontalAlignment.RIGHT);//数字右对齐
									cell.setCellStyle(styleCenter);
								}else{
									cell.setCellValue(cellV);
									cell.setCellStyle(styleLeft);
								}

							 } catch (ParseException e) {
								cell.setCellValue(cellV);
								cell.setCellStyle(styleLeft);
								//e.printStackTrace();
							 }
						}

					}

				}

			}
			if(rowInfo.containsKey("children")){
				JSONArray children=rowInfo.getJSONArray("children");
				int size=getChildrenSize(rowInfo,0);
				beginRow+=size;
				writeExcelList(book,sheet,colsInfo,children,beginRow,startCell,colMap);
			}

		}
		// 设置每个单元格宽度根据字多少自适应
//		for (int k = 0; k < colsInfo.size(); k++) {
//			String key=cellField[k];
//			int length = colMap.get(key);
//			//sheet.setColumnWidth(k+startCell, length * 280);
//			sheet.setColumnWidth(k+startCell, length * 185);
//		}

		for (int k = 0; k < colsInfo.size(); k++) {
		String key=cellField[k];
		int length = colMap.get(key);
		//sheet.setColumnWidth(k+startCell, length * 280);
			if(!pictureKey.contains(key)){
				sheet.setColumnWidth(k, length * 230);
			}

	}
	  return null;
	}

	//获取列跨列信息
	public static int getColColspan(JSONObject col,int colspan) {
	    if(!col.containsKey("children")){
	    	colspan+=1;
	    }else{
	    	JSONArray children=col.getJSONArray("children");
	    	for(int i=0;i<children.size();i++){
	    		colspan=getColColspan(children.getJSONObject(i),colspan);
	    	}
	    }
	    return colspan;
	}

	//获取列跨列信息
	public static int getColRowspan(JSONObject col,int maxColLevel) {
	  if(!col.containsKey("children")){
		  if(col.containsKey("_level")){
			  return maxColLevel-col.getInt("_level")+1;
		  }

	  }
		 return 1;

	}

	//获取列信息最大层次
	public static int getMaxLevelOfCols(JSONArray cols,int maxLevel) {
	   for(int i=0;i<cols.size();i++){
		   JSONObject jo=cols.getJSONObject(i);
		   if(jo.containsKey("_level")){
			   if(jo.getInt("_level")>maxLevel){
				   maxLevel=jo.getInt("_level");
			   }
			   if(jo.containsKey("children")){
				   return getMaxLevelOfCols(jo.getJSONArray("children"),maxLevel);
			   }
		   }
	   }
		return maxLevel;
	}

	/*设置合并区域边框*/
	public static void setRegionStyle(XSSFSheet sheet, CellRangeAddress region, XSSFCellStyle styleCenter) {
		  for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
			   Row row = sheet.getRow(i);
			   if(row==null){
				   row=sheet.createRow(i);
			   }
			   for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
				XSSFCell cell = (XSSFCell) row.getCell(j);
				if(cell==null){
					cell=(XSSFCell) row.createCell(j);
				}
			    cell.setCellStyle(styleCenter);
			   }
		 }
	}

	public static int getChildrenSize(JSONObject o,int size){
		if(!o.containsKey("children")){
			return 0;
		}else{
			JSONArray children=o.getJSONArray("children");
			size+=children.size();
			for(int i=0;i<children.size();i++){
				JSONObject jo=children.getJSONObject(i);
				size=getChildrenSize(jo,size);
			}
		}
		return size;
	}

}
